﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dost.Khcn.Data.EntityModel;
using Dost.Khcn.Data.Interfaces;

namespace Dost.Khcn.Data.Implements
{
    public class ChuyenGiaDao : IChuyenGiaDao
    {
        public List<ChuyenGia> GetAll()
        {
            using (var db = new NLKHCNDataEntities())
            {
                return db.ChuyenGias.OrderBy(x => x.HoTen).ToList();
            }
        }

        public List<ChuyenGia> GetChuyenGias(int startIndex, int pageSize, string sorting, string name, out int totalRecords)
        {
            using (var db = new NLKHCNDataEntities())
            {
                var query = from item in db.ChuyenGias
                            select item;

                // filter
                if (!string.IsNullOrEmpty(name))
                {
                    query = query.Where(x => x.HoTen.Contains(name));
                }

                totalRecords = query.Count();

                return pageSize > 0
                           ? query.OrderBy(sorting).Skip(startIndex).Take(pageSize).ToList() //Paging
                           : query.ToList(); //No paging
            }
        }

        public ChuyenGia AddChuyenGia(ChuyenGia chuyenGia)
        {
            using (var db = new NLKHCNDataEntities())
            {
                db.ChuyenGias.Add(chuyenGia);
                db.SaveChanges();
            }

            return chuyenGia;
        }

        public ChuyenGia GetById(int id)
        {
            using (var db = new NLKHCNDataEntities())
            {
                return db.ChuyenGias.FirstOrDefault(x => x.Id == id);
            }
        }

        public void UpdateChuyenGia(ChuyenGia chuyenGia)
        {
            using (var db = new NLKHCNDataEntities())
            {
                db.ChuyenGias.Attach(chuyenGia);
                db.Entry(chuyenGia).State = EntityState.Modified;
                db.SaveChanges();
            }
        }

        public void DeleteChuyenGia(int id)
        {
            if (id <= 0) return;
            using (var db = new NLKHCNDataEntities())
            {
                db.DeleteChuyenGia(id);
            }
        }

        public List<ChuyenGiaView> GetChuyenGiaViews(int startIndex, int pageSize, string sorting, string hoTen, string tenDonVi, int hocHamId, int hocViId, out int totalRecords)
        {
            using (var db = new NLKHCNDataEntities())
            {
                var query = from item in db.ChuyenGiaViews
                            select item;

                // filter
                if (!string.IsNullOrEmpty(hoTen))
                {
                    query = query.Where(x => x.HoTen.Contains(hoTen));
                }
                if (!string.IsNullOrEmpty(tenDonVi))
                {
                    query = query.Where(x => x.TenDonVi.Contains(tenDonVi));
                }
                if (hocHamId > 0)
                {
                    query = query.Where(x => x.HocHamId == hocHamId);
                }
                if (hocViId > 0)
                {
                    query = query.Where(x => x.HocViId == hocViId);
                }

                totalRecords = query.Count();

                return pageSize > 0
                           ? query.OrderBy(sorting).Skip(startIndex).Take(pageSize).ToList() //Paging
                           : query.ToList(); //No paging
            }
        }

        /// <summary>
        /// Tra cuu chuyen gia
        /// </summary>
        /// <param name="startIndex">Vi tri</param>
        /// <param name="pageSize">Trang</param>
        /// <param name="sorting">Sap xep</param>
        /// <param name="tenDonVi">Tra cuu theo don vi</param>
        /// <param name="tenCongTrinh">Tra cuu theo cong trinh.</param>
        /// <param name="linhVucId">Tra cuu theo linh vuc</param>
        /// <param name="hocHamId">Tra cuu theo hoc ham</param>
        /// <param name="hocViId">Tra cuu theo hoc vi</param>
        /// <param name="chuyenNganhId">Tra cuu theo chuyen nganh</param>
        /// <param name="totalRecords">So luong records.</param>
        /// <returns></returns>
        public List<SearchChuyenGiaView> SearchChuyenGia(int startIndex, int pageSize, string sorting, string tenDonVi,
            string tenCongTrinh, int linhVucId, int hocHamId, int hocViId, int chuyenNganhId, out int totalRecords)
        {
            using (var db = new NLKHCNDataEntities())
            {
                var query = from item in db.SearchChuyenGiaViews
                            select item;

                // filter
                if (linhVucId > 0)
                {
                    query = query.Where(x => x.LinhVucId == linhVucId);
                }
                if (hocHamId > 0)
                {
                    query = query.Where(x => x.HocHamId == hocHamId);
                }
                if (hocViId > 0)
                {
                    query = query.Where(x => x.HocViId == hocViId);
                }
                if (chuyenNganhId > 0)
                {
                    query = query.Where(x => x.ChuyenNganhId == chuyenNganhId);
                }
                if (!string.IsNullOrEmpty(tenDonVi))
                {
                    query = query.Where(x => x.TenDonVi.Contains(tenDonVi));
                }
                if (!string.IsNullOrEmpty(tenCongTrinh))
                {
                    query = query.Where(x => x.TenCongTrinh.Contains(tenCongTrinh));
                }

                totalRecords = query.Count();
                
                return pageSize > 0
                            ? query.OrderBy(sorting).Skip(startIndex).Take(pageSize).ToList() //Paging
                            : query.ToList(); //No paging
            }
        }

        public object GetDetailsById(int id)
        {
            using (var db = new NLKHCNDataEntities())
            {
                return db.ChuyenGiaDetailViews.FirstOrDefault(x => x.Id == id);
            }
        }

        public object GetInfoDetailsById(int id)
        {
            ChuyenGiaDetailView chuyenGiaInfo;

            using (var db = new NLKHCNDataEntities())
            {
                chuyenGiaInfo = db.ChuyenGiaDetailViews.FirstOrDefault(x => x.Id == id);
                if (chuyenGiaInfo == null) return null;

                chuyenGiaInfo.QuaTrinhCongTacList = db.QuaTrinhCongTacs.Where(x => x.ChuyenGiaId == id).ToList();
                chuyenGiaInfo.CongTrinhNghienCuuList = db.CongTrinhNghienCuus.Where(x => x.ChuyenGiaId == id).ToList();
                chuyenGiaInfo.DeTaiCapNhaNuocList = db.DeTaiCapNhaNuocViews.Where(x => x.ChuyenGiaId == id).ToList();
                chuyenGiaInfo.DeTaiCapTinhList = db.DeTaiCapTinhViews.Where(x => x.ChuyenGiaId == id).ToList();
                chuyenGiaInfo.GiaiThuongKhcnList = db.GiaiThuongKhcns.Where(x => x.ChuyenGiaId == id).ToList();
                chuyenGiaInfo.FileList = db.ChuyenGiaFileViews.Where(x => x.ChuyenGiaId == id).ToList();
            }

            return chuyenGiaInfo;
        }

        public List<ReportLinhVuc_Result> ReportLinhVuc()
        {
            using (var db = new NLKHCNDataEntities())
            {
                return db.ReportLinhVuc().ToList();
            }
        }
    }
}
